
var mysql  = require('mysql')
require('./function/function.js')

var ConnectionConfig = 
{
host: 'localhost', 
user:'root',
password : 's123',
port: '3306',
dateStrings: true,
multipleStatements: true
}

/////////数据结构配置/////////////////

var userDataForSql = ['id','name','gold']  //数据库是这个结构


/////////////////////////////////////////
//初始话数据库
exports.initMysql = function()
{
	var connection = mysql.createConnection(ConnectionConfig)
	var sql1 = 'create database if not exists dbl;'
	var sql2 = 'create table if not exists dbl.user (' //表名取userData phpmyadmin莫名报错
	for(var i in userDataForSql)
	{	
		if (userDataForSql[i]=='id')
			sql2 = sql2 + '`id` int(11) primary key not null auto_increment'
		else
			sql2 = sql2 + '`' + userDataForSql[i] + '` text'
		if (i<userDataForSql.length-1) sql2 = sql2 + ','
	}
	sql2 = sql2 + ')ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;'

    connection.query(sql1, 
 	function(err, rows, fields) 
 	{ 
 		if(err){console.log(err.message);connection.end();return;} 	    
	    initDbl()
    })
	 var initDbl = function()
	{
	 	connection.query(sql2, 
	 	function(err, rows, fields)
	 	{ 
				if(err){console.log(err.message);connection.end();return;} 	   
				_initProcedures(connection)
				//connection.end() 
		})
	}
}

//当内存中无用户数据时 会去数据库拉取 有三种情况1、玩家socket退出重进  2、新用户 3、服务器宕机,内存数据丢失 
exports.getUserByNameAsyn = function(name, callBack)
{	
	var connection = mysql.createConnection(ConnectionConfig)

	var sql = 'call dbl.getUserByName(' + '"' + name + '"' +   ',@a,@q);'
	connection.query(sql,function (err, retsult) {
        if(err){
            console.log('[EXEC PROC ERROR] - ',err.message)
            callBack('getUserByNameAsyn:fail')
            connection.end()
            return;
        }        
    	console.log(retsult,retsult[0])
    	var ud = retsult[0][0]
    	console.log('21',ud)
    	ud.state = 'free' // 这个是内存数据 如果宕机了 这个数据默认free
    	ud.roomId = 1000  
    	callBack(0, ud)
	    connection.end()
    })
}


//传入需要update的键值对
exports.updateUserByIdAsyn = function(id, updateUserData, callBack)
{
	var connection = mysql.createConnection(ConnectionConfig)
	var sql = 'UPDATE `dbl`.`user` SET '
	var params = []
	for(var i in updateUserData)
	{	
		sql = sql + i + '= ? '
		params[params.length] = updateUserData[i]
		if (i<updateUserData.length-1) sql = sql + ','
	}
	sql = sql + ' WHERE id = ' + id

	connection.query(sql,params,
	function (err, rows) 
	{
		if(err){
			console.log(err.message);
			callBack('updateUserByIdAsyn:update fail')
			connection.end();return;
		} 
		callBack(0, updateUserData)
		connection.end()
	})

}

exports.createRoomAsyn = function(userData, roomData, callBack)
{	
	var env = this
	var userId 
	for(var id in roomData.desksData)
		userId = id
	var connection = mysql.createConnection(ConnectionConfig)
	if (userData.state=='free')
	{
		var element = roomDataElement
		var sqlStr = getInsertSqlStrByElement('`dbl`.`room`',element)
		var rd = roomData
		rd.id = null
		var params = getInsertParamsByData(rd,element)
		connection.query(sqlStr,params, 
			function(err, rows) 
			{ 
				if(err){
					console.log(err.message)
					callBack('createRoomAsyn:insert fail')
					connection.end()
					return
				} 
				rd.id = rows.insertId

				env.updateUserByIdAsyn(userId,{state:'ready'},
					function(ret, updateUserData)
					{	
						if (ret==0)
						{
							callBack(0,rd)
	 						connection.end()
	 					}
	 					else
	 					{
	 						callBack(ret)
	 						connection.end()
	 					}
					})
			})
	}
	else
	{
		var sql1 = 'SELECT * FROM `dbl`.`room` '
		connection.query(sql1, function(err, rows, fields) 
		{ 
			if(err){
				console.log(err.message);
				callBack('quickJoinAsyn:select fail')
				connection.end();return;
			} 	
			for(var i in rows)
			{
				var rd = rows[i]
		    	rd.desksData = eval('(' + rd.desksData + ')')  //字符串转obj
		    	rd.deskDataModel =  eval('(' + rd.deskDataModel + ')') 

				var desksData = rd.desksData
				if (desksData[userId])
				{
					callBack(0, rd)
				    connection.end()
				    return 
				}
			}
			env.updateUserByIdAsyn(userId, {state:'free'},function(){})
			callBack('用户状态错误,请重试')
		    connection.end()
		})

	}

}

exports.quickJoinAsyn = function(userData, callBack)
{	
	var connection = mysql.createConnection(ConnectionConfig)
	var env = this

	if (userData.state=='free')
	{
		var sql1 = 'SELECT * FROM `dbl`.`room` WHERE `goldLimit` <= ' + userData.gold + ';'
		connection.query(sql1, function(err, rows, fields) { 
			if(err){
				console.log(err.message);
				callBack('quickJoinAsyn:select fail')
				connection.end();return;
			} 	
		    if (rows[0])  
		    {	
		    	var hasFreeDesk = false
		    	for(var i in rows)
		    	{	
		    		var rd = rows[i]
		    		rd.desksData = eval('(' + rd.desksData + ')')  //字符串转obj
		    		rd.deskDataModel =  eval('(' + rd.deskDataModel + ')') 
		    		var usingDeskNum = getObjLength(rd.desksData)
		    		if (parseInt(usingDeskNum)  < parseInt(rd.deskNum))
		    		{
		  				hasFreeDesk = true
		    			//更新数据库deskData 然后返回客户端
						var d = deepCopy(rd.deskDataModel) 
		                d.userId = userData.id
		                d.userName = userData.name
		                rd.desksData[userData.id] =  d

						var sql1 = 'UPDATE `dbl`.`room` SET desksData =' + "'" +  xToString(rd.desksData)  + "'" + ' WHERE id ='  + rd.id
						var sql2 = 'UPDATE `dbl`.`user` SET state = "ready" WHERE id ='  + userData.id
						connection.query(sql1+';'+sql2,
							function (err, rows) 
							{
								if(err){
									console.log(err.message);
									callBack('quickJoinAsyn:update fail')
									connection.end();return;
								} 
				                callBack(0, rd)
				                connection.end()
							})
						return
		    		}
		    	}
		    	if(!hasFreeDesk)
		    	{
		    		callBack('房间满了') //游戏币满足的房间没空桌子
		    		connection.end()
		    	}
		    }
		    else
		    {
		    	callBack('游戏币不足')
		    	connection.end()
		    }
		}) 


	}
	else
	{
		var sql1 = 'SELECT * FROM `dbl`.`room` '
		connection.query(sql1, function(err, rows, fields) 
		{ 
			if(err){
				console.log(err.message);
				callBack('quickJoinAsyn:select fail')
				connection.end();return;
			} 	
			for(var i in rows)
			{
				var rd = rows[i]
		    	rd.desksData = eval('(' + rd.desksData + ')')  //字符串转obj
		    	rd.deskDataModel =  eval('(' + rd.deskDataModel + ')') 

				var desksData = rd.desksData
				if (desksData[userData.id])
				{
					callBack(0, rd)
				    connection.end()
				    return 
				}
			}
			env.updateUserByIdAsyn(userData.id, {state:'free'},function(){})
			callBack('用户状态错误,请重试')
		    connection.end()
		})

	}


}


exports.leaveRoomAsyn = function(userData, roomData, callBack)
{	
	var connection = mysql.createConnection(ConnectionConfig)

	var desksData = roomData.desksData
	delete desksData[userData.id]

	var sql1 = 'UPDATE `dbl`.`room` SET desksData =' + "'" +  xToString(desksData)  + "'" + ' WHERE id ='  + roomData.id
	var sql2 = 'UPDATE `dbl`.`user` SET state = "free" WHERE id ='  + userData.id

	connection.query(sql1+';'+sql2,
		function (err, rows) 
		{
			if(err){
				console.log(err.message);
				callBack('leaveRoomAsyn:update fail')
				connection.end();return;
			} 
            callBack(0, roomData)
            connection.end()
		})


}

////////////////////////////////////////////////////////////
getInsertSqlStrByElement = function(targetStr,element)
{
	var sql1 = '('
	var sql2 = 'VALUES('
	for (var i=0;i<element.length;i++)
	{
		sql1 = sql1 + element[i] 
		sql2 = sql2 + '?'
		if (i == element.length -1){
			sql1 = sql1 + ')'
			sql2 = sql2 + ')'
		}
		else{
			sql1 = sql1 + ','
			sql2 = sql2 + ','
		}
	}
	var sql = 'INSERT INTO ' + targetStr + sql1 + sql2

	return sql
}

getInsertParamsByData = function(data,element)
{
	var  params = []
	for (var i=0;i<element.length;i++)
	{	
		params[i] = xToString(data[element[i]] )  
		console.log('INSERT params:',params[i])   
	}
	return params
}

_initProcedures= function(connection)
{
	connection = connection || mysql.createConnection(ConnectionConfig)
	//getUserByName
	var sql = 'CREATE PROCEDURE dbl.getUserByName(IN inName text, OUT outId int, OUT outGold text)	' + '\n' + 
	'BEGIN 																							' + '\n' + 
	'	declare outId int default null; 															' + '\n' + 
	'	declare outGold text default 100000; 														' + '\n' + 
	'	select id,gold into outId,outGold from user where name = inName;							' + '\n' + 
	'	if outId then 																				' + '\n' + 
	'    	select * from user where name = inName; 												' + '\n' + 
	'    else 																						' + '\n' + 
	'    	insert into user(id, name, gold) values(outId, inName, outGold); 						' + '\n' + 
	'    	while row_count()>0 do 																	' + '\n' + 
	'    		select * from user where name = inName; 											' + '\n' + 
	'    	end while; 																				' + '\n' + 
	'    end if; 																					' + '\n' + 
	'END' 
	connection.query(sql,function (err, retsult) {
        if(err){
            console.log('[_initProcedures error] - ',err.message);
            connection.end()
            return;
        }        
    	console.log(retsult)
    	connection.end()
    })
}
